﻿Public Class ConsultaFarmaciaDAO
    Public Shared Function insertarConsultaFarmacia(ByVal ConsultaFarmaciaMotivo As String, ByVal ConsultaFarmaciaFecha As DateTime, ByVal ConsultaFarmaciaFechaLimite As DateTime, ByVal TipoConsultaId As Integer, ByVal EmpleadoId As Integer, ByVal RepresentanteFarmaciaId As Integer, ByVal FarmaciaId As Integer, ByVal EstadoConsultaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(ConsultaFarmaciaMotivo)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_ConsultaFarmacia"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.INSERTAR).DbType = DbType.Int32
                    .AddWithValue("@ConsultaFarmaciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@ConsultaFarmaciaMotivo", ConsultaFarmaciaMotivo).DbType = DbType.String
                    .AddWithValue("@ConsultaFarmaciaFecha", ConsultaFarmaciaFecha).DbType = DbType.DateTime
                    .AddWithValue("@ConsultaFarmaciaFechaLimite", ConsultaFarmaciaFechaLimite).DbType = DbType.DateTime
                    .AddWithValue("@TipoConsultaId", TipoConsultaId).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoId", EmpleadoId).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaId", RepresentanteFarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@EstadoConsultaId", Constante.ESTADO.NoSolucionada).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerConsultasFarmacias(ByVal ConsultaFarmaciaId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_ConsultaFarmacia_obtener", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@ConsultaFarmaciaId", ConsultaFarmaciaId).DbType = DbType.Int32
            da.Fill(ds, "ConsultasFarmacias")

            Return ds.Tables("ConsultasFarmacias")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function actualizarConsultaFarmacia(ByVal ConsultaFarmaciaId As Integer, ByVal ConsultaFarmaciaMotivo As String, ByVal ConsultaFarmaciaFecha As DateTime, ByVal ConsultaFarmaciaFechaLimite As DateTime, ByVal TipoConsultaId As Integer, ByVal EmpleadoId As Integer, ByVal RepresentanteFarmaciaId As Integer, ByVal FarmaciaId As Integer, ByVal EstadoConsultaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            Constante.esNulo(ConsultaFarmaciaMotivo)
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_ConsultaFarmacia"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ACTUALIZAR).DbType = DbType.Int32
                    .AddWithValue("@ConsultaFarmaciaId", ConsultaFarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@ConsultaFarmaciaMotivo", ConsultaFarmaciaMotivo).DbType = DbType.String
                    .AddWithValue("@ConsultaFarmaciaFecha", ConsultaFarmaciaFecha).DbType = DbType.DateTime
                    .AddWithValue("@ConsultaFarmaciaFechaLimite", ConsultaFarmaciaFechaLimite).DbType = DbType.DateTime
                    .AddWithValue("@TipoConsultaId", TipoConsultaId).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoId", EmpleadoId).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaId", RepresentanteFarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@EstadoConsultaId", EstadoConsultaId).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()

            End With
            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function eliminarConsultaFarmacia(ByVal ConsultaFarmaciaId As Integer) As Boolean
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim consulta As New SqlClient.SqlCommand
            With consulta
                .CommandText = "usp_ConsultaFarmacia"
                .CommandType = CommandType.StoredProcedure
                With .Parameters
                    .AddWithValue("@Accion", Constante.ELIMINAR).DbType = DbType.Int32
                    .AddWithValue("@ConsultaFarmaciaId", ConsultaFarmaciaId).DbType = DbType.Int32
                    .AddWithValue("@ConsultaFarmaciaMotivo", Constante.VACIO).DbType = DbType.String
                    .AddWithValue("@ConsultaFarmaciaFecha", Constante.FECHAVACIA).DbType = DbType.DateTime
                    .AddWithValue("@ConsultaFarmaciaFechaLimite", Constante.FECHAVACIA).DbType = DbType.DateTime
                    .AddWithValue("@TipoConsultaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@EmpleadoId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@RepresentanteFarmaciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@FarmaciaId", Constante.MENOSUNO).DbType = DbType.Int32
                    .AddWithValue("@EstadoConsultaId", Constante.MENOSUNO).DbType = DbType.Int32
                End With
                .Connection = cn
                .ExecuteNonQuery()
            End With

            Return True
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return False
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function obtenerConsultasFarmaciasCalendario(ByVal FarmaciaId As Integer, ByVal EstadoConsultaId As Integer, ByVal TipoConsultaId As Integer, ByVal EmpleadoId As Integer) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_consultaFarmacia_calendario", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            With da.SelectCommand.Parameters
                .AddWithValue("@EstadoConsultaId", EstadoConsultaId).DbType = DbType.Int32
                .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                .AddWithValue("@TipoConsultaId", TipoConsultaId).DbType = DbType.Int32
                .AddWithValue("@EmpleadoId", EmpleadoId).DbType = DbType.Int32
            End With

            da.Fill(ds, "ConsultasFarmaciasCalendario")

            Return ds.Tables("ConsultasFarmaciasCalendario")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function buscarConsultasFarmacias(ByVal FarmaciaId As Integer, ByVal TipoConsultaId As Integer, ByVal EstadoConsultaId As Integer, ByVal ConsultaFarmaciaFechaDesde As DateTime, ByVal ConsultaFarmaciaFechaHasta As DateTime) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_consultaFarmacia_buscar", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            da.SelectCommand.Parameters.AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@TipoConsultaId", TipoConsultaId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@EstadoConsultaId", EstadoConsultaId).DbType = DbType.Int32
            da.SelectCommand.Parameters.AddWithValue("@ConsultaFarmaciaFechaDesde", ConsultaFarmaciaFechaDesde).DbType = DbType.DateTime
            da.SelectCommand.Parameters.AddWithValue("@ConsultaFarmaciaFechaHasta", ConsultaFarmaciaFechaHasta).DbType = DbType.DateTime
            da.Fill(ds, "ConsultasFarmacias")

            Return ds.Tables("ConsultasFarmacias")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

    Public Shared Function filtroConsultasFarmacias(ByVal FarmaciaId As Integer, ByVal EstadoConsultaId As Integer, ByVal TipoConsultaId As Integer, ByVal EmpleadoId As Integer, ByVal ConsultaFarmaciaFechaDesde As Date, ByVal ConsultaFarmaciaFechaHasta As Date) As DataTable
        Dim cn As SqlClient.SqlConnection = Nothing
        Try
            cn = Conexion.Conectar
            Dim ds As New DataSet
            Dim da As New SqlClient.SqlDataAdapter("usp_consultaFarmacia_filtro", cn)
            da.SelectCommand.CommandType = CommandType.StoredProcedure
            With da.SelectCommand.Parameters
                .AddWithValue("@EstadoConsultaId", EstadoConsultaId).DbType = DbType.Int32
                .AddWithValue("@FarmaciaId", FarmaciaId).DbType = DbType.Int32
                .AddWithValue("@TipoConsultaId", TipoConsultaId).DbType = DbType.Int32
                .AddWithValue("@EmpleadoId", EmpleadoId).DbType = DbType.Int32
                .AddWithValue("@ConsultaFarmaciaFechaDesde", ConsultaFarmaciaFechaDesde).DbType = DbType.Date
                .AddWithValue("@ConsultaFarmaciaFechaHasta", ConsultaFarmaciaFechaHasta).DbType = DbType.Date
            End With

            da.Fill(ds, "ConsultasFarmaciasCalendario")

            Return ds.Tables("ConsultasFarmaciasCalendario")
        Catch ex As Exception
            'GUARDAMOS EL ERROR EN LA BD
            ErrorDAO.insertarError(ex)
            Return Nothing
        Finally
            Conexion.Desconectar(cn) 'PASAR POR PARAMETRO LA CONEXION
        End Try
    End Function

End Class
